package org.restql.db;


import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.restql.db.resource.RequestResource;

public class WhereClauseGenerator {
	public static String getWhereString(RequestResource requestResource, List<String> where) {
		String predicate = " ";
		if (where.size() > 0) {
			predicate += "WHERE " + Utility.ands(where);
		}
		return predicate + WhereClauseGenerator.generateWhereClause(requestResource.getQueryParameters(), false);
	}

	public static String getFromString(RequestResource requestResource) {
		return "FROM " + requestResource.getResourceName();
	}

	public static String generateSelectString(RequestResource requestResource, boolean multiTable, 
			List<String> select, List<String> where, String connectString, String defaultMax, String defaultSkip){
			String selectHeader = "SELECT " + Utility.commas(select);
			String selectFrom = getFromString(requestResource);
			String selectWhere = getWhereString(requestResource, where);
			String sql=null;
			String skip=null;
			String max=null;
			for (String name : (Set<String>)requestResource.getQueryParameters().keySet()) {
				if (name.startsWith("rql:max")) {
					max= ((String[])requestResource.getQueryParameters().get(name))[0]
					.replaceFirst("rql:max", "");
				}else if(name.startsWith("rql:skip")) {
					skip= ((String[])requestResource.getQueryParameters().get(name))[0]
					.replaceFirst("rql:skip", "");
				}
			}
			max = (max == null && defaultMax != null) ? defaultMax : max;
			skip = (skip == null && defaultSkip != null) ? defaultSkip : skip;
			
			if (max != null || skip != null) {
				if (connectString.startsWith("jdbc:oracle:")) {
					if (max != null) {
						String tempSelectHeader = selectHeader;
						if (select.size() >= 1 && select.get(0).trim().equals("*")) {
							tempSelectHeader = "SELECT " + requestResource.getResourceName() 
							+"."+Utility.commas(select);
						}
						sql = tempSelectHeader  + ", ROWNUM RESTQL_TMP__ROWNUM" 
						+ "   " + selectFrom
						+ "   " + ((selectWhere.trim().length() != 0) ? selectWhere : " WHERE ")
						+ "   ROWNUM <= " + ((skip != null) ? String.valueOf(Long.valueOf(max) + Long.valueOf(skip)) : max);
					}
					if (skip != null) {
						sql = selectHeader 
						+ " FROM (" + sql + ")"
						+ " WHERE RESTQL_TMP__ROWNUM >= " + skip;
					} else {
						sql = selectHeader 
						+ " FROM (" + sql + ")";
					}
				} else if (connectString.startsWith("jdbc:sqlserver:")) { // before sqlserver 2005
					sql = "SELECT TOP " + max 
					+ " " + requestResource.getResourceName() + "." + Utility.commas(select) 
					+ " " + selectFrom 
					+ " " + selectWhere;
				} else if (// connectString.startsWith("jdbc:sqlserver:") ||
						// sqlserver requires row_number() over(ORDER BY...),
						// so that cannot implement without dictionary operation
					connectString.startsWith("jdbc:sybase:")) { // set rowcount
																// n; set
																// rowcount 0;
					if (max != null) {
						String tempSelectHeader = selectHeader;
						if (select.size() >= 1 && select.get(0).trim().equals("*")) {
							tempSelectHeader = "SELECT " + requestResource.getResourceName() 
							+"."+Utility.commas(select);
						}
						sql = tempSelectHeader  + ", ROW_NUMBER() OVER () RESTQL_TMP__ROWNUM" 
						+ "   " + selectFrom
						+ "   " + selectWhere;
					}
					if (max != null && skip != null) {
						sql = selectHeader 
						+ " FROM (" + sql + ")"
						+ " WHERE RESTQL_TMP__ROWNUM > " + skip + " AND "  
						+ " RESTQL_TMP__ROWNUM < "  
						+ ((skip != null) ? String.valueOf(Long.valueOf(max) + Long.valueOf(skip)) : max);
					} else if (max != null && skip == null) {
						skip = "0";
						sql = selectHeader 
						+ " FROM (" + sql + ")"
						+ " WHERE RESTQL_TMP__ROWNUM > " + skip + " AND "  
						+ " RESTQL_TMP__ROWNUM < "  
						+ ((skip != null) ? String.valueOf(Long.valueOf(max) + Long.valueOf(skip)) : max);
					} else if (max == null && skip != null) {
						sql = selectHeader 
						+ " FROM (" + sql + ")"
						+ " WHERE RESTQL_TMP__ROWNUM < "  
						+ ((skip != null) ? String.valueOf(Long.valueOf(max) + Long.valueOf(skip)) : max);
					}
				} else if (connectString.startsWith("jdbc:mysql:") 
						|| connectString.startsWith("jdbc:db2:") // offset
						|| connectString.startsWith("jdbc:postgre:")
						) {
					if (max != null) {
						selectWhere += " LIMIT " + max;
					}
					if (skip != null) {
						selectWhere +=  " OFFSET " + skip;
					}
					sql = selectHeader 
					+ "   " + selectFrom
					+ "   " + selectWhere;
				} else {
					new RuntimeException("Unsupported jdbc url ");
				}
			} else {
				sql = selectHeader 
				+ "   " + selectFrom
				+ "   " + selectWhere;
			}
			return sql;
	}
	
	public static String generateWhereClause(Map queryMap, boolean multiTable) {
		String whereClause = "";
		String max = null;
		String skip = null;
		String callback = null;
		for (Object key : queryMap.keySet()) {
			if (((String) key).equals("callback") ) {
				callback = ((String []) queryMap.get(key))[0];
			}
			// Limit amount of records
			else if ( ((String) key).startsWith("rql:max") ) {
				max = ((String []) queryMap.get(key))[0];
			}
			// Skip forward through records
			else if ( ((String) key).startsWith("rql:skip") ) {
				skip = ((String []) queryMap.get(key))[0];
			}
			// Basic query param
			else {
				if (whereClause.length() == 0) whereClause = " WHERE ";
				if (whereClause.length() > 7) whereClause += " AND ";

				String val = ((String []) queryMap.get(key))[0];
				String operator = " = ";

				// Prefixes other than '='
				if (val.contains(":")) {
					String parts[] = val.split(":");
					val = parts[1];
					operator = mapToOp(parts[0]);
				}

				whereClause += key + operator + "'" + val + "'";
			}
		}

		/*
		 * if (max != null && skip != null) { whereClause += " LIMIT " + skip +
		 * "," + max; } else if (max != null && skip == null) { whereClause += "
		 * LIMIT " + max; }
		 */

		if (multiTable && whereClause.length() > 0) whereClause = " AND " + whereClause.substring(6);

		return whereClause;
	}

	protected static String mapToOp(String op) {
		if (op.equalsIgnoreCase("lt")) return " < ";
		else if (op.equals("lte")) return " <= ";
		else if (op.equals("gte")) return " >= ";
		else if (op.equals("gt")) return " > ";
		else if (op.equals("ne")) return " != ";
		else return " = ";
	}

}
